/******************************************************************************
 * @File name   :      ReportDaoImpl.java
 *
 * @Author      :      JIZHANGS
 *
 * @Date        :      Sep 25, 2015
 *
 * @Copyright Notice: 
 * Copyright (c) 2015 Capgemini, Inc. All  Rights Reserved.
 * This software is published under the terms of the Capgemini Software
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file.
 * 
 * 
 * ----------------------------------------------------------------------------
 * Date                   Who         Version        Comments
 * Sep 25, 2015 11:35:40 AM        JIZHANGS     1.0            Initial Version
 *****************************************************************************/
package com.cap.bts.crmdp.dealer.report.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManagerFactory;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.jpa.support.JpaDaoSupport;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.report.dao.IReportDao;
import com.cap.bts.crmdp.dealer.report.dto.TranHistoryDto;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.exception.AppException;

/**
 *
 */
@Repository("reportDao")
public class ReportDaoImpl extends JpaDaoSupport implements IReportDao {

    @Autowired
    public ReportDaoImpl(EntityManagerFactory entityManagerFactory) {
        setEntityManagerFactory(entityManagerFactory);
    }

    // @PersistenceContext
    // private EntityManager entityManager
    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Sep 25, 2015
     * @see com.cap.bts.crmdp.dealer.report.dao.IReportDao#getTranHistoryListByMemberId(java.lang.Long,
     *      java.lang.Long)
     **/
    @Override
    public List<TranHistoryDto> getTranHistoryListByMemberId(Long memeberId, Long dealerId,String carName)
        throws AppException {
        String sqlString = "select * from (SELECT p.dealer_id,p.member_id,purchase_date,c.car_plate,'套餐购买' as transaction_type,pd.name,p.package_number,'' as ticket_name,'' as ticket_number,'' as so_number,p.STATUS"
            + " FROM tm_member_package_info p,"
            + " tm_raw_car_plate c,"
            + " tm_crm_package_definition pd"
            + " WHERE p.car_id = c.id"
            + " AND p.package_def_id = pd.id" + " AND p.DEALER_ID ="
            + dealerId
            + " AND p.MEMBER_ID ="
            + memeberId
            + " AND c.car_plate ='"
            + carName+"'"
            + " UNION ALL"
            + " SELECT"
            + " active_dealer_id,active_member_id,active_date,c.car_plate,'套餐激活' as transaction_type,pd.name,p.package_number,'' as ticket_name,'' as ticket_number,'' so_number,p.STATUS"
            + " FROM tm_member_package_info p,"
            + " tm_raw_car_plate c,"
            + " tm_crm_package_definition pd"
            + " WHERE p.active_car_id = c.id"
            + " AND p.package_def_id = pd.id"
            + " AND p.ACTIVE_DEALER_ID ="
            + dealerId
            + " AND p.ACTIVE_MEMBER_ID ="
            + memeberId
            + " AND c.car_plate ='"
            + carName+"'"
            + " UNION ALL"
            + " SELECT active_dealer_id,active_member_id,active_date,c.car_plate,'套餐使用' as transaction_type,pd.name,p.package_number,t.ticket_name,r.ACTIVATION_CODE as ticket_number,sales_order as so_number,p.STATUS"
            + " FROM tm_member_package_info p,"
            + " tm_raw_car_plate c,"
            + " tm_crm_package_definition pd,"
            + " tm_member_package_redemption r,"
            + " tm_raw_package_redemption_lines rl,"
            + " tm_raw_ticket_def t"
            + " WHERE p.active_car_id = c.id"
            + " AND p.package_def_id = pd.id"
            + " AND p.id = r.package_id"
            + " AND r.line_id = rl.id"
            + " AND rl.ticket_def_id = t.id"
            + " AND r.status = 1"
            + " AND p.ACTIVE_DEALER_ID ="
            + dealerId
            + " AND p.ACTIVE_MEMBER_ID ="
            + memeberId
            + " AND c.car_plate ='"
            + carName+"'"
            + " UNION ALL"
            + " SELECT c.redeem_dealer_id,c.redeem_member_id,c.redeem_date,c.REDEEM_MEMBER_CAR_PLATE,'优惠券使用' as transaction_type,'','',t.ticket_name,c.coupon_number as ticket_number,'' as so_number,'3'"
            + " FROM tm_raw_coupon_redeem c,"
            + " tm_raw_ticket_def t,tm_raw_coupon d"
            + " WHERE d.ticket_def_id = t.id"
            + " AND c.COUPON_ID = d.ID"
            + " AND c.REDEEM_DEALER_ID =" + dealerId + " AND c.REDEEM_MEMBER_ID =" + memeberId
            + " AND c.REDEEM_MEMBER_CAR_PLATE ='"
            + carName+"') a order by a.purchase_date desc";
        List<Object[]> list = this.getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sqlString));
        List<TranHistoryDto> listDtos = null;
        if (list != null && !list.isEmpty()) {
            listDtos = new ArrayList<TranHistoryDto>();
            TranHistoryDto dto = null;
            for (int i = 0; i < list.size(); i++) {
                dto = new TranHistoryDto();
                dto.setDealerId(Long.valueOf(list.get(i)[0].toString()));
                dto.setMemberId(Long.valueOf(list.get(i)[1].toString()));
                dto.setPurchaseDate((Date) list.get(i)[2]);
                dto.setCarPlateName((String) list.get(i)[3]);
                dto.setTranName((String) list.get(i)[4]);
                dto.setPackageName((String) list.get(i)[5]);
                dto.setPackgeNumber((String) list.get(i)[6]);
                dto.setTicketName((String) list.get(i)[7]);
                dto.setTicketNumber((String) list.get(i)[8]);
                dto.setOrderNumber((String) list.get(i)[9]);
                dto.setStatus(Byte.parseByte(list.get(i)[10].toString()));
                listDtos.add(dto);
            }
        }
        return listDtos;
        // return this.getJpaTemplate().execute(new
        // NativeQueryJpaCallback(sqlString,Object[].class))
    }

}
